﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using WMSADBUtility;
using WMSAML.SY;

namespace WMSADL.SY
{
    public class TUsers_DAL
    {
        /// <summary>
        /// 
        /// </summary>
        public int Add_Users(TUsers_Entity entity)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into TUsers(");
            strSql.Append("Organization_id,Login_name,Username,password,Mobile,Email,Creat_time,Login_time,Last_login_time,Count,EnableStatus,LoginStatus,LoginIP)");

            strSql.Append(" values (");
            strSql.Append("@Organization_id,@Login_name,@Username,@password,@Mobile,@Email,@Creat_time,@Login_time,@Last_login_time,@Count,@EnableStatus,@LoginStatus,@LoginIP)");
            strSql.Append(";select @@IDENTITY");
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
            db.AddInParameter(dbCommand, "Organization_id", DbType.Int32, entity.Organization_id);
            db.AddInParameter(dbCommand, "Login_name", DbType.String, entity.Login_name);
            db.AddInParameter(dbCommand, "Username", DbType.String, entity.Username);
            db.AddInParameter(dbCommand, "password", DbType.String, entity.password);
            db.AddInParameter(dbCommand, "Mobile", DbType.String, entity.Mobile);
            db.AddInParameter(dbCommand, "Email", DbType.String, entity.Email);
            db.AddInParameter(dbCommand, "Creat_time", DbType.DateTime, entity.Creat_time);
            db.AddInParameter(dbCommand, "Login_time", DbType.DateTime, entity.Login_time);
            db.AddInParameter(dbCommand, "Last_login_time", DbType.DateTime, entity.Last_login_time);
            db.AddInParameter(dbCommand, "Count", DbType.Int32, entity.Count);
            db.AddInParameter(dbCommand, "EnableStatus", DbType.Int32, entity.EnableStatus);
            db.AddInParameter(dbCommand, "LoginStatus", DbType.Int32, entity.LoginStatus);
            db.AddInParameter(dbCommand, "LoginIP", DbType.String, entity.LoginIP);
            int result;
            object obj = db.ExecuteScalar(dbCommand);
            if (!int.TryParse(obj.ToString(), out result))
            {
                return 0;
            }
            return result;
        }

        /// <summary>
        /// 用户ID-(不允许删除用户信息，修改用户状态)
        /// </summary>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE TUsers SET EnableStatus=0 ");
            strSql.Append(" WHERE User_id=@User_id ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "User_id", DbType.Int32, id);
                int rows = db.ExecuteNonQuery(dbCommand);

                if (rows > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(TUsers_Entity entity)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update TUsers set ");
            strSql.Append("Organization_id=@Organization_id,");
            strSql.Append("Login_name=@Login_name,");
            strSql.Append("Username=@Username,");
            strSql.Append("password=@password,");
            strSql.Append("Mobile=@Mobile,");
            strSql.Append("Email=@Email,");
            strSql.Append("Creat_time=@Creat_time,");
            strSql.Append("Login_time=@Login_time,");
            strSql.Append("Last_login_time=@Last_login_time,");
            strSql.Append("Count=@Count,");
            strSql.Append("EnableStatus=@EnableStatus,");
            strSql.Append("LoginStatus=@LoginStatus,");
            strSql.Append("LoginIP=@LoginIP");
            strSql.Append(" where User_id=@User_id ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "User_id", DbType.Int32, entity.User_id);
                db.AddInParameter(dbCommand, "Organization_id", DbType.Int32, entity.Organization_id);
                db.AddInParameter(dbCommand, "Login_name", DbType.String, entity.Login_name);
                db.AddInParameter(dbCommand, "Username", DbType.String, entity.Username);
                db.AddInParameter(dbCommand, "password", DbType.String, entity.password);
                db.AddInParameter(dbCommand, "Mobile", DbType.String, entity.Mobile);
                db.AddInParameter(dbCommand, "Email", DbType.String, entity.Email);
                db.AddInParameter(dbCommand, "Creat_time", DbType.DateTime, entity.Creat_time);
                db.AddInParameter(dbCommand, "Login_time", DbType.DateTime, entity.Login_time);
                db.AddInParameter(dbCommand, "Last_login_time", DbType.DateTime, entity.Last_login_time);
                db.AddInParameter(dbCommand, "Count", DbType.Int64, entity.Count);
                db.AddInParameter(dbCommand, "EnableStatus", DbType.Int32, entity.EnableStatus);
                db.AddInParameter(dbCommand, "LoginStatus", DbType.Int32, entity.LoginStatus);
                db.AddInParameter(dbCommand, "LoginIP", DbType.String, entity.LoginIP);
                int rows = db.ExecuteNonQuery(dbCommand);

                if (rows > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool UpdateStatus(int status,int uid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE TUsers SET ");
            strSql.Append("LoginStatus=@LoginStatus ");
            strSql.Append(" WHERE User_id=@User_id ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "User_id", DbType.Int32, uid);
                db.AddInParameter(dbCommand, "LoginStatus", DbType.Int32, status);
                int rows = db.ExecuteNonQuery(dbCommand);
                if (rows > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        /// <summary>
        ///  用户编号是否存在
        /// </summary>
        public bool ExistsByID(int id)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(1) FROM TUsers WHERE User_id=@User_id ");
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "User_id", DbType.Int32, id);
                int cmdresult;
                object obj = db.ExecuteScalar(dbCommand);
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    cmdresult = 0;
                }
                else
                {
                    cmdresult = int.Parse(obj.ToString());
                }
                if (cmdresult == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        /// <summary>
        ///  Oid是否存在
        /// </summary>
        public bool ExistsByOID(int Oid)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(1) FROM TUsers WHERE Organization_id=@Organization_id ");
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Organization_id", DbType.Int32, Oid);
                int cmdresult;
                object obj = db.ExecuteScalar(dbCommand);
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    cmdresult = 0;
                }
                else
                {
                    cmdresult = int.Parse(obj.ToString());
                }
                if (cmdresult == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        /// <summary>
        /// 用户登录名是否存在
        /// </summary>
        public bool ExistsByName(string login_name)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(1) FROM TUsers WHERE Login_name=@Login_name ");
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Login_name", DbType.String, login_name);
                int cmdresult;
                object obj = db.ExecuteScalar(dbCommand);
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    cmdresult = 0;
                }
                else
                {
                    cmdresult = int.Parse(obj.ToString());
                }
                if (cmdresult == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        /// <summary>
        /// 登录验证
        /// </summary>
        public bool Validate_Login(string loginName, string pwd)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT COUNT(1) FROM TUsers WHERE Login_name=@Login_name AND password=@password ");
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Login_name", DbType.String, loginName);
                db.AddInParameter(dbCommand, "password", DbType.String, pwd);
                int cmdresult;
                object obj = db.ExecuteScalar(dbCommand);
                if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                {
                    cmdresult = 0;
                }
                else
                {
                    cmdresult = int.Parse(obj.ToString());
                }
                if (cmdresult == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        /// <summary>
        /// 用户登录名-对象实体
        /// </summary>
        /// <summary>
        public TUsers_Entity GetEntityByName(string loginName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select User_id,Organization_id,Login_name,Username,password,Mobile,Email,Creat_time,Login_time,Last_login_time,Count,EnableStatus,LoginStatus,LoginIP from TUsers ");
            strSql.Append(" where Login_name=@Login_name ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Login_name", DbType.String, loginName);
                TUsers_Entity entity = new TUsers_Entity();
                using (IDataReader dataReader = db.ExecuteReader(dbCommand))
                {
                    if (dataReader.Read())
                    {
                        entity.User_id = Convert.ToInt32(dataReader["User_id"].ToString());
                        entity.Organization_id = Convert.ToInt32(dataReader["Organization_id"].ToString());
                        entity.Login_name = dataReader["Login_name"].ToString();
                        entity.Username = dataReader["Username"].ToString();
                        entity.password = dataReader["password"].ToString();
                        entity.Mobile = dataReader["Mobile"].ToString();
                        entity.Email = dataReader["Email"].ToString();
                        entity.Creat_time = Convert.ToDateTime(dataReader["Creat_time"].ToString());
                        entity.Login_time = Convert.ToDateTime(dataReader["Login_time"].ToString());
                        entity.Last_login_time = Convert.ToDateTime(dataReader["Last_login_time"].ToString());
                        entity.Count = Convert.ToInt32(dataReader["Count"].ToString());
                        entity.EnableStatus = Convert.ToInt32(dataReader["EnableStatus"].ToString());
                        entity.LoginStatus = Convert.ToInt32(dataReader["LoginStatus"].ToString());
                        entity.LoginIP = dataReader["LoginIP"].ToString();
                    }
                }
                return entity;
            }
        }

        /// <summary>
        /// 用户ID-对象实体
        /// </summary>
        /// <summary>
        public TUsers_Entity GetEntityByID(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select User_id,Organization_id,Login_name,Username,password,Mobile,Email,Creat_time,Login_time,Last_login_time,Count,EnableStatus,LoginStatus,LoginIP from TUsers ");
            strSql.Append(" where User_id=@User_id ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "User_id", DbType.Int32, id);
                TUsers_Entity entity = new TUsers_Entity();
                using (IDataReader dataReader = db.ExecuteReader(dbCommand))
                {
                    if (dataReader.Read())
                    {
                        entity.User_id = Convert.ToInt32(dataReader["User_id"].ToString());
                        if (dataReader["Organization_id"].ToString() == "")
                            entity.Organization_id = 0;
                        else
                            entity.Organization_id = Convert.ToInt32(dataReader["Organization_id"].ToString());
                        entity.Login_name = dataReader["Login_name"].ToString();
                        entity.Username = dataReader["Username"].ToString();
                        entity.password = dataReader["password"].ToString();
                        entity.Mobile = dataReader["Mobile"].ToString();
                        entity.Email = dataReader["Email"].ToString();
                        entity.Creat_time = Convert.ToDateTime(dataReader["Creat_time"].ToString());
                        entity.Login_time = Convert.ToDateTime(dataReader["Login_time"].ToString());
                        entity.Last_login_time = Convert.ToDateTime(dataReader["Last_login_time"].ToString());
                        entity.Count = Convert.ToInt32(dataReader["Count"].ToString());
                        entity.EnableStatus = Convert.ToInt32(dataReader["EnableStatus"].ToString());
                        entity.LoginStatus = Convert.ToInt32(dataReader["LoginStatus"].ToString());
                        entity.LoginIP = dataReader["LoginIP"].ToString();
                    }
                }
                return entity;
            }
        }

        /// <summary>
        /// ByO_id获得用户数据列表
        /// </summary>
        public DataSet GetList_TUsersByO_id(int id)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder strSql = new StringBuilder();
            DataSet ds = new DataSet();
            using (DbCommand dbCommand = db.GetStoredProcCommand("ALPrcList_TUserRMByO_id"))
            {
                db.AddInParameter(dbCommand, "Organization_id", DbType.String, id);
                ds = db.ExecuteDataSet(dbCommand);
                return (ds.Tables[0].Rows.Count > 0) ? ds : null;
            }
        }

        /// <summary>
        /// ByU_id获得用户数据列表（用户权限设计,获取用户对应的角色和组）
        /// </summary>
        public DataSet GetList_TUsersByU_id(int id, string username)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder strSql = new StringBuilder();
            DataSet ds = new DataSet();
            using (DbCommand dbCommand = db.GetStoredProcCommand("ALPrcList_TUserRMByU_id"))
            {
                db.AddInParameter(dbCommand, "User_id", DbType.Int32, id);
                db.AddInParameter(dbCommand, "User_name", DbType.String, username);
                ds = db.ExecuteDataSet(dbCommand);
                return (ds.Tables[0].Rows.Count > 0) ? ds : null;
            }
        }

        /// <summary>
        /// V 用户ID-得对象实体
        /// </summary>
        /// <summary>
        public Vlh_TUserRM_Entity GetEntityVByID(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT User_id,Organization_id,Organization_name,Group_id,Group_name,Role_id,Role_name,Login_name,Username,password,Mobile,Email,Creat_time,Login_time,Last_login_time,Count,EnableStatus,LoginStatus,LoginIP FROM Vlh_TUserRM ");
            strSql.Append(" WHERE User_id=@User_id ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "User_id", DbType.Int32, id);
                Vlh_TUserRM_Entity entity = new Vlh_TUserRM_Entity();
                using (IDataReader dataReader = db.ExecuteReader(dbCommand))
                {
                    if (dataReader.Read())
                    {
                        entity.User_id = Convert.ToInt32(dataReader["User_id"].ToString());
                        if (dataReader["Organization_id"].ToString() == "")
                            entity.Organization_id = 0;
                        else
                            entity.Organization_id = Convert.ToInt32(dataReader["Organization_id"].ToString());
                        entity.Organization_name = dataReader["Organization_name"].ToString();
                        entity.Group_id = dataReader["Group_id"].ToString();
                        entity.Group_name = dataReader["Group_name"].ToString();
                        entity.Role_id = dataReader["Role_id"].ToString();
                        entity.Role_name = dataReader["Role_name"].ToString();
                        entity.Login_name = dataReader["Login_name"].ToString();
                        entity.Username = dataReader["Username"].ToString();
                        entity.password = dataReader["password"].ToString();
                        entity.Mobile = dataReader["Mobile"].ToString();
                        entity.Email = dataReader["Email"].ToString();
                        entity.Creat_time = Convert.ToDateTime(dataReader["Creat_time"].ToString());
                        entity.Login_time = Convert.ToDateTime(dataReader["Login_time"].ToString());
                        entity.Last_login_time = Convert.ToDateTime(dataReader["Last_login_time"].ToString());
                        entity.Count = Convert.ToInt32(dataReader["Count"].ToString());
                        entity.EnableStatus = Convert.ToInt32(dataReader["EnableStatus"].ToString());
                        entity.LoginStatus = Convert.ToInt32(dataReader["LoginStatus"].ToString());
                        entity.LoginIP = dataReader["LoginIP"].ToString();
                    }
                }
                return entity;
            }
        }

        /// <summary>
        /// V 用户名-得对象实体
        /// </summary>
        /// <summary>
        public Vlh_TUserRM_Entity GetEntityVByUserName(string loginName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT User_id,Organization_id,Organization_name,Group_id,Group_name,Role_id,Role_name,Login_name,Username,password,Mobile,Email,Creat_time,Login_time,Last_login_time,Count,EnableStatus,LoginStatus,LoginIP FROM Vlh_TUserRM ");
            strSql.Append(" WHERE Login_name=@Login_name ");
            Database db = DatabaseFactory.CreateDatabase();
            using (DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString()))
            {
                db.AddInParameter(dbCommand, "Login_name", DbType.String, loginName);
                Vlh_TUserRM_Entity entity = new Vlh_TUserRM_Entity();
                using (IDataReader dataReader = db.ExecuteReader(dbCommand))
                {
                    if (dataReader.Read())
                    {
                        entity.User_id = Convert.ToInt32(dataReader["User_id"].ToString());
                        if (dataReader["Organization_id"].ToString() == "")
                            entity.Organization_id = 0;
                        else
                            entity.Organization_id = Convert.ToInt32(dataReader["Organization_id"].ToString());
                        entity.Organization_name = dataReader["Organization_name"].ToString();
                        entity.Group_id = dataReader["Group_id"].ToString();
                        entity.Group_name = dataReader["Group_name"].ToString();
                        entity.Role_id = dataReader["Role_id"].ToString();
                        entity.Role_name = dataReader["Role_name"].ToString();
                        entity.Login_name = dataReader["Login_name"].ToString();
                        entity.Username = dataReader["Username"].ToString();
                        entity.password = dataReader["password"].ToString();
                        entity.Mobile = dataReader["Mobile"].ToString();
                        entity.Email = dataReader["Email"].ToString();
                        entity.Creat_time = Convert.ToDateTime(dataReader["Creat_time"].ToString());
                        entity.Login_time = Convert.ToDateTime(dataReader["Login_time"].ToString());
                        entity.Last_login_time = Convert.ToDateTime(dataReader["Last_login_time"].ToString());
                        entity.Count = Convert.ToInt32(dataReader["Count"].ToString());
                        entity.EnableStatus = Convert.ToInt32(dataReader["EnableStatus"].ToString());
                        entity.LoginStatus = Convert.ToInt32(dataReader["LoginStatus"].ToString());
                        entity.LoginIP = dataReader["LoginIP"].ToString();
                    }
                }
                return entity;
            }
        }

    }
}
